package com.suypower.carManage.json;

import com.jarp.db.DAO;
import com.opensymphony.xwork2.ActionContext;
import com.opensymphony.xwork2.ActionSupport;
import com.suypower.carManage.VO.CarVO;
import com.suypower.carManage.VO.SecureVO;
import com.suypower.carManage.comm.ConstUtil;
import com.suypower.carManage.comm.SQLHelp;
import freemarker.template.utility.StringUtil;
import jxl.Workbook;
import jxl.format.UnderlineStyle;
import jxl.write.*;
import net.sf.json.JSONArray;

import javax.servlet.ServletException;
import java.io.*;
import java.util.*;
import org.apache.commons.lang3.StringUtils;

public class SecureJson extends ActionSupport {

    private String car;
    private String stime;
    private String etime;
    private String jq_stime;
    private String jq_etime;
    private String sy_stime;
    private String sy_etime;
    private String start = "1";
    private String cbCompany;

    public String getStime() {
        return stime;
    }

    public void setStime(String stime) {
        this.stime = stime;
    }

    public String getEtime() {
        return etime;
    }

    public void setEtime(String etime) {
        this.etime = etime;
    }

    public String getJq_stime() {
        return jq_stime;
    }

    public void setJq_stime(String jq_stime) {
        this.jq_stime = jq_stime;
    }

    public String getJq_etime() {
        return jq_etime;
    }

    public void setJq_etime(String jq_etime) {
        this.jq_etime = jq_etime;
    }

    public String getSy_stime() {
        return sy_stime;
    }

    public void setSy_stime(String sy_stime) {
        this.sy_stime = sy_stime;
    }

    public String getSy_etime() {
        return sy_etime;
    }

    public void setSy_etime(String sy_etime) {
        this.sy_etime = sy_etime;
    }

    private String limit = "20";
    private String _dc;
    InputStream excelStream;
    ActionContext cxt = ActionContext.getContext();
    Map<String, Object> session = cxt.getSession();
    String userid = (String) session.get("userid");
    private String excelTitle;
    private String title = "车辆保险记录";

    public String execute() {
        DAO dao = new DAO();
        String res = "";
        ArrayList<SecureVO> list = new ArrayList<SecureVO>();
        String sql = getSql();
        int iStart = Integer.parseInt(start);
        int iPageSize = Integer.parseInt(limit);
        int iPage = iStart / iPageSize + 1;
        Vector v;
        try {
            v = dao.getDataSet(ConstUtil.DBNAME, sql + " order by t.id desc ", iPage, iPageSize);
            if (v != null) {
                for (Object obj : v) {
                    Hashtable hash = (Hashtable) obj;
                    SecureVO secure = new SecureVO();
                    secure.setId((String) hash.get("ID"));
                    secure.setCar_code((String) hash.get("CAR_CODE"));
                    secure.setInsurer((String) hash.get("INSURER"));
                    secure.setInsurance_jq_stime((String) hash.get("INSURANCE_JQ_STIME"));
                    secure.setInsurance_jq_etime((String) hash.get("INSURANCE_JQ_ETIME"));
                    secure.setInsurance_jq_warranty((String) hash.get("INSURANCE_JQ_WARRANTY"));
                    secure.setInsurance_commercial_stime((String) hash.get("INSURANCE_COMMERCIAL_STIME"));
                    secure.setInsurance_commercial_etime((String) hash.get("INSURANCE_COMMERCIAL_ETIME"));
                    secure.setPremium((String) hash.get("PREMIUM"));
                    secure.setPolicy((String) hash.get("POLICY"));
                    secure.setInsurance_jq_premium((String) hash.get("INSURANCE_JQ_PREMIUM"));
                    secure.setVesselusetax((String) hash.get("VESSELUSETAX"));
                    list.add(secure);
                }

                String totalCountSQL = "select count(1) CNT from (" + sql + " ) zz ";
                String totalCount = dao.getDataValue(ConstUtil.DBNAMEODS, totalCountSQL);
                JSONArray json = JSONArray.fromObject(list);
                ActionContext.getContext().put("data", "{totalCount:" + totalCount + ",data:" + json.toString() + "}");
            } else {
                ActionContext.getContext().put("data", "{totalCount:" + 0 + ",data:''}");
            }
        } catch (ServletException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return SUCCESS;
    }

    public String getSql() {
        StringBuilder sql = new StringBuilder("SELECT * FROM tb_secure t WHERE 1=1");
        car = SQLHelp.splitStrFromWeb(car);
        if (!StringUtils.isEmpty(car)) {
            sql.append(SQLHelp.createSqlInWithAlias("car_code", "t", car));
        }
        if (!StringUtils.isEmpty(jq_stime)) {
            sql.append(" and  t.insurance_jq_etime  >= '").append(jq_stime).append("' ");
        }
        if (!StringUtils.isEmpty(jq_etime)) {
            sql.append(" and  t.insurance_jq_etime  <= '").append(jq_etime).append("' ");
        }

        if (!StringUtils.isEmpty(sy_stime)) {
            sql.append(" and  t.insurance_commercial_etime  >= '").append(sy_stime).append("' ");
        }
        if (!StringUtils.isEmpty(sy_etime)) {
            sql.append(" and t.insurance_commercial_etime <= '").append(sy_etime).append("'");
        }
        cbCompany = SQLHelp.splitStrFromWeb(cbCompany);
        if (!StringUtils.isEmpty(cbCompany)) {
            sql.append(SQLHelp.createSqlInWithAlias("insurer", "t", cbCompany));
        }
        return sql.toString();
    }

    public String getExcel() throws UnsupportedEncodingException, IOException, WriteException {
        System.out.println("导出Excel");
        excelStream = this.getExcelInputStream();
        return "excel";

    }

    public InputStream getExcelInputStream() throws IOException, WriteException {
        ByteArrayOutputStream out = new ByteArrayOutputStream();
        create(out);
        return new ByteArrayInputStream(out.toByteArray());
    }

    public void create(OutputStream out) throws IOException, WriteException {
        DAO dao = new DAO();
        excelTitle = title + ".xls";
        excelTitle = new String(excelTitle.getBytes(), "ISO8859-1");
        List<String> count_name = new ArrayList<String>();
        ;    //用来记录具体的列名
        count_name.add("车牌号码");
        count_name.add("承保公司");
        count_name.add("交强险开始时间");
        count_name.add("交强险结束时间");
        count_name.add("交强险保单号");
        count_name.add("交强险保费");
        count_name.add("车船使用税");
        count_name.add("商业保险开始时间");
        count_name.add("商业保险结束时间");
        count_name.add("商业保险费");
        count_name.add("保单号");

        WritableWorkbook book = Workbook.createWorkbook(out);
        WritableSheet sheet = book.createSheet(title, 0);
        sheet.setRowView(0, 810);//设置大标题格式
        for (int i = 2; i < 100; i++) {
            sheet.setRowView(i, 320);
        }
        sheet.setColumnView(0, 18);
        sheet.setColumnView(1, 12);//设置第一行的宽度  //注意宽度和高度的单位不相同
        sheet.setColumnView(2, 30);
        sheet.setColumnView(3, 30);
        sheet.setColumnView(4, 30);
        sheet.setColumnView(5, 17);
        sheet.setColumnView(6, 17);
        sheet.setColumnView(7, 17);
        sheet.setColumnView(8, 25);
        sheet.setColumnView(9, 25);
        sheet.setColumnView(10, 35);
        sheet.setColumnView(11, 25);
        sheet.setColumnView(12, 25);
        sheet.setColumnView(13, 25);
        sheet.setColumnView(14, 25);
        sheet.setColumnView(15, 25);
        sheet.setColumnView(16, 25);
        sheet.setColumnView(17, 25);

        WritableFont wfc = new WritableFont(WritableFont.createFont("黑体"), 16, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK);
        WritableCellFormat wcfFC = new WritableCellFormat(wfc);
        wcfFC.setAlignment(jxl.format.Alignment.CENTRE); // 设置横向对齐方式
        wcfFC.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE); //设置纵向对齐方式
        // wcfFC.setBorder(Border.ALL, BorderLineStyle.THIN);  //设置单元格边框和边框线条样式
        sheet.addCell(new Label(0, 0, title, wcfFC));

        //设置小标题格式
        WritableFont wfc2 = new WritableFont(WritableFont.createFont("黑体"), 11, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK);
        WritableCellFormat wcfFC2 = new WritableCellFormat(wfc2);
        wcfFC2.setAlignment(jxl.format.Alignment.CENTRE); // 设置横向对齐方式
        wcfFC2.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE); //设置纵向对齐方式
        wcfFC2.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);  //设置单元格边框和边框线条样式
        wcfFC2.setWrap(true);

        //设置普通文字格式
        WritableFont wfc3 = new WritableFont(WritableFont.createFont("宋体"), 10, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK);
        WritableCellFormat wcfFC3 = new WritableCellFormat(wfc3);
        wcfFC3.setAlignment(jxl.format.Alignment.CENTRE); // 设置横向对齐方式
        wcfFC3.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE); //设置纵向对齐方式
        wcfFC3.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);  //设置单元格边框和边框线条样式
        wcfFC3.setWrap(true);
        sheet.addCell(new Label(0, 0, title, wcfFC));
        sheet.mergeCells(0, 0, count_name.size() - 1, 0);
        for (int i = 0; i < count_name.size(); i++) {
            try {

                sheet.addCell(new Label(i, 1, count_name.get(i).split(",")[0], wcfFC2));
            } catch (Exception ex) {
            }
        }
        ArrayList<CarVO> list = new ArrayList<CarVO>();
        String sql = this.getSql();
        Vector v = null;
        try {
            v = dao.getDataSet(ConstUtil.DBNAME, sql);

        } catch (ServletException e) {
            e.printStackTrace();
        }
        if (v != null) {

            for (int i = 1; i < v.size() + 2; i++) {
                sheet.setRowView(i, 400);
            }
            int hang = 2;     //数据应该写在多少行
            for (int i = 0; i < v.size(); i++) {
                ArrayList<String> rowlist = new ArrayList<String>();
                Hashtable hash = (Hashtable) v.get(i);
                SecureVO secure = new SecureVO();
                secure.setId((String) hash.get("ID"));
                secure.setCar_code((String) hash.get("CAR_CODE"));
                secure.setInsurer((String) hash.get("INSURER"));
                secure.setInsurance_jq_stime((String) hash.get("INSURANCE_JQ_STIME"));
                secure.setInsurance_jq_etime((String) hash.get("INSURANCE_JQ_ETIME"));
                secure.setInsurance_jq_warranty((String) hash.get("INSURANCE_JQ_WARRANTY"));
                secure.setInsurance_commercial_stime((String) hash.get("INSURANCE_COMMERCIAL_STIME"));
                secure.setInsurance_commercial_etime((String) hash.get("INSURANCE_COMMERCIAL_ETIME"));
                secure.setPremium((String) hash.get("PREMIUM"));
                secure.setPolicy((String) hash.get("POLICY"));
                secure.setInsurance_jq_premium((String) hash.get("INSURANCE_JQ_PREMIUM"));
                secure.setVesselusetax((String) hash.get("VESSELUSETAX"));
                rowlist.add(secure.getCar_code());
                rowlist.add(secure.getInsurer());
                rowlist.add(secure.getInsurance_jq_stime());
                rowlist.add(secure.getInsurance_commercial_etime());
                rowlist.add(secure.getInsurance_jq_warranty());
                rowlist.add(secure.getInsurance_jq_premium());
                rowlist.add(secure.getVesselusetax());
                rowlist.add(secure.getInsurance_commercial_stime());
                rowlist.add(secure.getInsurance_commercial_etime());
                rowlist.add(secure.getPremium());
                rowlist.add(secure.getPolicy());
                for (int i2 = 0; i2 < rowlist.size(); i2++) {
                    String thisvalue = rowlist.get(i2);
                    if (thisvalue == null || thisvalue.equals("")) {
                        thisvalue = "";
                    }
                    sheet.addCell(new Label(i2, hang, thisvalue, wcfFC3));
                }
                hang++;
            }
            book.write();
            book.close();
        }
    }

    public InputStream getExcelStream() {
        return excelStream;
    }

    public void setExcelStream(InputStream excelStream) {
        this.excelStream = excelStream;
    }

    public String getExcelTitle() {
        return excelTitle;
    }

    public void setExcelTitle(String excelTitle) {
        this.excelTitle = excelTitle;
    }

    public String getCar() {
        return car;
    }

    public void setCar(String car) {
        this.car = car;
    }

    public String getStart() {
        return start;
    }

    public void setStart(String start) {
        this.start = start;
    }

    public String getLimit() {
        return limit;
    }

    public void setLimit(String limit) {
        this.limit = limit;
    }

    public String get_dc() {
        return _dc;
    }

    public void set_dc(String dc) {
        _dc = dc;
    }

    public String getCbCompany() {
        return cbCompany;
    }

    public void setCbCompany(String cbCompany) {
        this.cbCompany = cbCompany;
    }

}
